﻿using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.IO;
using System.Data.OleDb;
using System.Collections;
using Excel = Microsoft.Office.Interop.Excel;

namespace Common
{
    public class CComOLEDBHelper
    {

        #region OLEDB访问CSV、txt
        /// <summary>
        /// 文本驱动
        /// </summary>
        //string ConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Text;'";

        string ConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES'";  

        //string ConnStr ;
  

        public CComOLEDBHelper(string strConnStr)
        {
            //ConnStr = strConnStr;
        }







        /// <summary>
        /// 查询CSV文件记录
        /// </summary>
        /// <param name="directory">目录</param>
        /// <param name="fileName">文件名(带后缀)</param>
        /// <param name="tj">查询条件(例如："WHERE 姓名 = '凌老大'"，如无条件则为null)</param>
        /// <returns></returns>
        public DataTable SelectCSV(string directory, string fileName, string tj)
        {
            DataSet ds = new DataSet();

            OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format("select * from [{0}] " + (tj != null ? tj : ""), fileName), string.Format(ConnStr, directory));
            adapter.Fill(ds);
            return ds.Tables[0];
        }
        /// <summary>
        /// 给CSV文件添加记录
        /// </summary>
        /// <param name="directory">目录</param>
        /// <param name="fileName">文件名(带后缀)</param>
        /// <param name="values">添加记录值(例如"'凌老大',20,'男'")</param>
        /// <returns></returns>
        public int InsertCSV(string directory, string fileName, string values)
        {
            int row = 0;
            using (OleDbConnection conn = new OleDbConnection(string.Format(ConnStr, directory)))
            {
                conn.Open();
                string sql = "INSERT INTO [{0}] VALUES({1})";
                OleDbCommand comm = new OleDbCommand(string.Format(sql, fileName, values), conn);
                row = comm.ExecuteNonQuery();
            }
            return row;
        }
        #endregion

    }
}
